﻿using System;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using Libraries.Common;
using Libraries.Entities;

namespace Libraries.Repositories
{
    public static class Extention
    {
        public static string ToStringOrDefault(this object obj) {
            return obj == null ? string.Empty : obj.ToString();
        }
    }

    public class AccountCertificateRepository
    {
        private AccountCertificate GetAccountCertificate(IDataRecord dataReader) {
            if (dataReader != null) {
                var acc = new AccountCertificate();

                acc.EmployeeCertificateId = (int)dataReader["EmployeeCertificateId"];
                acc.EmployeeId = dataReader["EmployeeId"].ToStringOrDefault();
                acc.AccountName = dataReader["AccountName"].ToStringOrDefault();
                acc.CertificateCategory = dataReader["CertificateCategory"].ToStringOrDefault();
                acc.CertificateName = dataReader["CertificateName"].ToStringOrDefault();
                acc.CertificateProvider = dataReader["CertificateProvider"].ToStringOrDefault();
                acc.CompletedDate = dataReader["CompletedDate"].ToDateTimeOrNull();
                acc.ContractEndDate = dataReader["ContractEndDate"].ToDateTimeOrNull();
                int currencyId;
                if (int.TryParse(dataReader["CurrencyId"].ToStringOrDefault(), out currencyId))
                    acc.CurrencyId = currencyId;

                //acc.CurrencyId=dataReader["CurrencyId"].ToInt32OrDefault();

                acc.CurrencyName = dataReader["CurrencyName"].ToStringOrDefault();
                acc.Department = dataReader["Department"].ToStringOrDefault();
                acc.DepartmentId = (int)dataReader["DepartmentId"];
                float fee;
                if (float.TryParse(dataReader["Fee"].ToStringOrDefault(), out fee))
                    acc.Fee = fee;
                acc.FullName = dataReader["FullName"].ToStringOrDefault();
                float refunds;
                if (float.TryParse(dataReader["Refunds"].ToStringOrDefault(), out refunds))
                    acc.Refunds = refunds;
                acc.Score = dataReader["Score"].ToStringOrDefault();
                int timeComit;
                if (int.TryParse(dataReader["TimeCommit"].ToStringOrDefault(), out timeComit))
                    acc.TimeCommit = (int?)dataReader["TimeCommit"];
                acc.TotalRow = (int)dataReader["TotalRow"];

                return acc;
            }
            return null;
        }

        private Certificate GetCertificate(IDataRecord record) {
            if (record != null) {
                return new Certificate {
                                           CertificateCategoryId = (int?)record["CertificateCategoryId"],
                                           CertificateCategoryName = record["CertificateCategoryName"].ToStringOrDefault(),
                                           ParentCertificateCategoryId = (int?)record["ParentCertificateCategoryId"],
                                           Note = record["Note"].ToStringOrDefault()
                                       };
            }
            return null;
        }

        public Collection<AccountCertificate> Search(int pageSize, int currentPage, int certificateId,
            string employeeId, string certificateProvider, string certificateCategory,
            string accountName, string certificateName, string score,
            bool isCertificateNode, int statusAccountId) {
            var lists = new Collection<AccountCertificate>();


            var parameters = new Collection<SqlParameter>();

            parameters.Add(new SqlParameter("@PageSize", pageSize));
            parameters.Add(new SqlParameter("@CurrentPage", currentPage));
            parameters.Add(new SqlParameter("@CertificateId", certificateId));

            if (!string.IsNullOrEmpty(employeeId)) {
                parameters.Add(new SqlParameter("@EmployeeId", employeeId));
            }

            if (!string.IsNullOrEmpty(certificateProvider)) {
                parameters.Add(new SqlParameter("@CertificateProviderName", certificateProvider));
            }

            if (!string.IsNullOrEmpty(accountName)) {
                parameters.Add(new SqlParameter("@AccountName", accountName));
            }

            if (!string.IsNullOrEmpty(certificateName)) {
                parameters.Add(new SqlParameter("@CertificateName", certificateName));
            }

            if (!string.IsNullOrEmpty(score)) {
                parameters.Add(new SqlParameter("@Score", score));
            }

            parameters.Add(new SqlParameter("@IsCertificateNode", isCertificateNode));
            parameters.Add(new SqlParameter("@StatusAccountId", statusAccountId));

            using (IDataReader result = RepositorySql.SqlHelper.ExecuteReader(ProcedureConfiguration.AccountCertificateSearch, parameters, ConnectionConfiguration.ConnectionString)) {
                if (result != null) {
                    while (result.Read()) {
                        lists.Add(GetAccountCertificate(result));
                    }
                }
            }

            return lists;
        }

        public Collection<Certificate> Certificates() {
            var list = new Collection<Certificate>();
            using (IDataReader result = RepositorySql.SqlHelper.ExecuteReader(ProcedureConfiguration.Certificates, null, ConnectionConfiguration.ConnectionString)) {
                if (result != null) {
                    while (result.Read()) {
                        list.Add(GetCertificate(result));
                    }
                }
            }
            return list;
        }

        //QuyenNTH
        public Certificate CertificateDetailByCertificateId(int certificateId) {
            var parameter = new Collection<SqlParameter> {
                new SqlParameter("@CertificateId",certificateId)
                                                         };
            using (IDataReader result = RepositorySql.SqlHelper.ExecuteReader(ProcedureConfiguration.CertificateDetailByCertificateId, parameter, ConnectionConfiguration.ConnectionString))
            {
                if (result != null)
                {
                    while (result.Read()) {
                        var certificate = new Certificate();
                        certificate.CertificateId = (int)result["CertificateId"];
                        certificate.CertificateName = (string)result["CertificateName"];
                        certificate.CertificateCategoryId = (int)result["CertificateCategoryId"];
                        certificate.CertificateCategoryName = (string)result["CertificateCategoryName"];
                        certificate.CertificateProviderId = (int)result["CertificateProviderId"];
                        certificate.CertificateProviderName = (string)result["CertificateProviderName"];
                        certificate.TimeCommit = result["TimeCommit"].ToInt32OrDefault();
                        certificate.Note = (string)result["Note"];
                        return certificate;
                    }
                }
            }
            return null;
        }
    }
}